package com.example.demo.dao;
import com.example.demo.config.JdbcConfiguration;
import com.example.demo.model.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.sql.*;
import java.util.ArrayList;

@Component
public class JdbcMysql {
    @Autowired
    private JdbcConfiguration jdbcConfiguration;

     public void insert(Test test)throws ClassNotFoundException, SQLException {
         Connection connection = jdbcConfiguration.getConnection();
         //需要执行的sql语句
         String sql = "insert into test_2(name,math_score,chinese_score,english_score,computer_score) values(?,?,?,?,?)";
         //获取预处理对象，并给参数赋值
         PreparedStatement statement = connection.prepareCall(sql);
         statement.setString(1,test.getName());
         statement.setDouble(2,test.getMathScore());
         statement.setDouble(3,test.getChineseScore());
         statement.setDouble(4,test.getEnglishScore());
         statement.setDouble(5,test.getComputerScore());
         //执行sql语句（插入了几条记录，就返回几）
         int i = statement.executeUpdate();  //executeUpdate：执行并更新
         //关闭jdbc连接
         statement.close();
         connection.close();
     }

    public void delete(Test test)throws ClassNotFoundException, SQLException {
        Connection connection = jdbcConfiguration.getConnection();
        //需要执行的sql语句
        String sql = "delete from test_2 where id=?";
        //获取预处理对象，并给参数赋值
        PreparedStatement statement = connection.prepareCall(sql);
        statement.setLong(1,test.getId());
        //执行sql语句（插入了几条记录，就返回几）
        int i = statement.executeUpdate();  //executeUpdate：执行并更新
        //关闭jdbc连接
        statement.close();
        connection.close();
    }

    public void change(Test test)throws ClassNotFoundException, SQLException {
        Connection connection = jdbcConfiguration.getConnection();
        //需要执行的sql语句
        String sql = "update test_2 set math_score=?,chinese_score=?,english_score=?,computer_score=? where id=?";
        //获取预处理对象，并给参数赋值
        PreparedStatement statement = connection.prepareCall(sql);
        statement.setDouble(1,test.getMathScore());
        statement.setDouble(2,test.getChineseScore());
        statement.setDouble(3,test.getEnglishScore());
        statement.setDouble(4,test.getComputerScore());
        statement.setLong(5,test.getId());
        //执行sql语句（插入了几条记录，就返回几）
        int i = statement.executeUpdate();  //executeUpdate：执行并更新
        //关闭jdbc连接
        statement.close();
        connection.close();
    }

    public ArrayList get(Test test)throws ClassNotFoundException, SQLException {
        Connection connection = jdbcConfiguration.getConnection();Object o;
        Statement state=connection.createStatement();
        //需要执行的sql语句
        String sql = "select * from test_2 where id=?";
        //获取预处理对象，并给参数赋值
        PreparedStatement statement = connection.prepareCall(sql);
        statement.setLong(1,test.getId());
        ResultSet rst = statement.executeQuery();
        ArrayList list = new ArrayList();
        while(rst.next()) {
            Test test1 = new Test();
            test1.setId(rst.getLong("id"));
            test1.setName(rst.getString("name"));
            test1.setMathScore(rst.getDouble("math_score"));
            test1.setChineseScore(rst.getDouble("chinese_score"));
            test1.setEnglishScore(rst.getDouble("english_score"));
            test1.setComputerScore(rst.getDouble("computer_score"));
            list.add(test1);
        }
        //关闭jdbc连接
        statement.close();
        connection.close();
        return list;
    }
}
